﻿using Microsoft.AspNetCore.Http;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;

namespace iWare.Wms.Core.ExcelHelper
{
    /// <summary>
    /// Excel导入导出类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 是否Excel文件
        /// </summary>
        /// <param name="fileName"></param>
        public static void CheckIsExcel(string fileName)
        {
            var allowExtension = new List<string>() { ".xlsx", ".xls" };
            var extension = Path.GetExtension(fileName);
            var fileOk = false;
            foreach (var item in allowExtension)
            {
                if (item.Equals(extension, StringComparison.InvariantCultureIgnoreCase)) fileOk = true;
            }
            if (!fileOk) throw new Exception("请选择EXCEL文件");
        }

        public static DataTable SheetToDataTableHasTitle(ISheet sheet)
        {
            DataTable dt = new DataTable();
            if (!string.IsNullOrWhiteSpace(sheet.SheetName))
            {
                dt.TableName = sheet.SheetName;
            }
            IRow firstRow = sheet.GetRow(0);
            if (firstRow != null)
            {
                for (int i = 0; i < firstRow.Cells.Count; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        var colName = firstRow.GetCell(i).ToString();
                        colName = Regex.Replace(colName, @"\s", "");
                        if (dt.Columns[colName] == null)
                        {
                            dt.Columns.Add(colName);
                        }
                        else
                        {
                            dt.Columns.Add();
                        }
                    }
                    else
                    {
                        dt.Columns.Add();
                    }
                }
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dataRow = dt.NewRow();
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    for (int j = 0; j < firstRow.Cells.Count; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = "";
                            continue;
                        }

                        switch (cell.CellType)
                        {
                            case CellType.Boolean:
                                dataRow[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Numeric:
                                //dataRow[j] = cell.NumericCellValue;
                                string value = string.Empty;
                                if (DateUtil.IsCellInternalDateFormatted(cell))
                                {
                                    value = DateTime.FromOADate(cell.NumericCellValue).ToString();
                                }
                                else if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    value = DateTime.FromOADate(cell.NumericCellValue).ToString();
                                }
                                //有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
                                else if (cell.CellStyle.GetDataFormatString() == null)
                                {
                                    value = DateTime.FromOADate(cell.NumericCellValue).ToString();
                                }
                                else
                                {
                                    value = cell.NumericCellValue.ToString();
                                }
                                dataRow[j] = value;
                                break;
                            case CellType.String:
                                dataRow[j] = cell.StringCellValue.Trim();
                                break;
                            case CellType.Formula:
                                try
                                {
                                    dataRow[j] = cell.StringCellValue.Trim();
                                }
                                catch (Exception)
                                {
                                    try
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    catch (Exception)
                                    {
                                        dataRow[j] = cell.ToString().Trim();
                                    }
                                }
                                break;
                            default:
                                dataRow[j] = cell.ToString().Trim();
                                break;
                        }
                    }
                    dt.Rows.Add(dataRow);
                }
            }

            return dt;
        }

        /// <summary>
        /// NPOI Sheet转Datatable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static DataTable SheetToDataTable(ISheet sheet)
        {
            if (sheet.LastRowNum <= 0)
            {
                return null;
            }

            DataTable dt = new DataTable(sheet.SheetName);

            int maxColumnCount = 0;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null || row.LastCellNum <= maxColumnCount)
                {
                    continue;
                }
                maxColumnCount = row.LastCellNum;
            }

            for (int i = 0; i < maxColumnCount; i++)
            {
                dt.Columns.Add();
            }

            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                DataRow dataRow = dt.NewRow();
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = 0; j < row.Cells.Count; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                    {
                        dataRow[j] = "";
                        continue;
                    }

                    switch (cell.CellType)
                    {
                        case CellType.Boolean:
                            dataRow[j] = cell.BooleanCellValue;
                            break;
                        case CellType.Numeric:
                            dataRow[j] = cell.NumericCellValue;
                            break;
                        case CellType.String:
                            dataRow[j] = cell.StringCellValue.Trim();
                            break;
                        case CellType.Formula:
                            try
                            {
                                dataRow[j] = cell.StringCellValue.Trim();
                            }
                            catch (Exception)
                            {
                                try
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                                catch (Exception)
                                {
                                    dataRow[j] = cell.ToString().Trim();
                                }
                            }
                            break;
                        default:
                            dataRow[j] = cell.ToString().Trim();
                            break;
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="file">导入文件</param>
        /// <returns>List<T></returns>
        public static List<T> InputExcel(IFormFile file)
        {
            List<T> list = new List<T> { };

            MemoryStream ms = new MemoryStream();
            file.CopyTo(ms);
            ms.Seek(0, SeekOrigin.Begin);

            IWorkbook workbook = new XSSFWorkbook(ms);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);
            var propertys = typeof(T).GetProperties();
            string value = null;
            int num = cellNum.LastCellNum;

            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    value = row.GetCell(j).ToString();
                    string str = (propertys[j].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[j].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, null, null);
                    }
                }

                list.Add(obj);
            }

            return list;
        }
    }
}
